1. Technical Field
The present invention is directed to the field of database queries. More specifically, the invention provides a system and method for configuring, sequencing and viewing joins in a database query.
2. Description of the Related Art
In today""s information economy, companies increasingly rely on information systems to remain competitive. Foremost among these information systems are database management systems that utilize relational databases. These systems are generally referred to as relational database management systems. Relational databases organize data records into a multitude of interconnected tables. Each table consists of rows and columns of data from the data records stored in the database. The rows of data are also known as tuples.
A relational database management system typically includes software for searching and retrieving data records from the relational database. The most common means for accomplishing this search and retrieval task is the Structured Query Language (SQL). SQL provides a software framework for logically structuring complex conditional expressions (i.e., relationships) for querying a database. SQL includes many different types of logical constructs, including the WHERE clause, the SELECT statement, the HAVING clause, and the ON clause. A WHERE clause is typically structured as follows: WHERE (variable 1  less than operator greater than  condition 1) link (variable 2  less than operator greater than  condition 2). The WHERE clause returns data records from the database that meet the two conditional expressions (variable 1  less than operator greater than  condition 2) link (variable 2  less than operator greater than  condition 2), depending on the type of link. Two common forms of link operators for conditional expressions are the xe2x80x9cANDxe2x80x9d link and the xe2x80x9cORxe2x80x9d link.
The SQL ON clause typically forms part of the SQL SELECT statement, and it is used to define a set of relationships (conditional expressions) that are associated with joining two or more tables. In SQL syntax, a join may be defined as a relational operation that allows a program to retrieve data from two or more tables based on matching column values. IBM Dictionary of Computing, 10th Edition, p. 365. The SQL join operation combines the data rows (or tuples) from two or more tables into a result set according to the relationships expressed in the ON clause, and also according to the type of join. The result set is a data table that includes the merged data rows from the two or more tables operated on by the join that meet the relationships set in the ON clause.
In an SQL statement that includes more than one join operation, there are two types of result sets, a final result set and one or more intermediate result sets. The final result set is the final table that is generated from the tables being joined after all the join operations are executed. The intermediate result set is the intermediate table that is generated from just two of the tables (or one table and another intermediate result set) being joined in one of the join operations. As discussed below, certain types of joins, such as the outer join, internally produce intermediate result sets.
A generic SQL statement for generating a result set from a join operation may be expressed as follows:
SELECT [Cols] from [Table 1] join type [Table 2] ON {CONDITION = less than relationship 1  greater than  operator  less than relationship 2 greater than  operator  less than relationship N greater than  }.
Here, Table 1 and Table 2 are the two tables operated on by the join. There are several different types of join that can be used to merge data from tables. The most common join types include the inner join, the left outer join, the right outer join, and the full outer join.
The condition of the ON clause includes one or more relationships coupled together by one or more operators. For example, relationship 1 could be xe2x80x9cTable1.column1 =Table2.column1xe2x80x9d and relationship 2 could be xe2x80x9cTable1.column2 =Table2.column2.xe2x80x9d The operator that couples relationship 1 to relationship 2 could be the AND operator, or it could be some other type of logical operator. In this example, the condition of the join is met when the data entry in the first column of the first table is equal to the data entry in the first column of the second table and where the data entry in the second column of the first table is equal to the data entry in the second column of the second table.
An inner join of two tables returns only the rows in each table that meet the condition in the ON clausexe2x80x94i.e., the matching rows. In the left, right and full outer joins, however, rows in one of the tables may be returned into the result set even though there are no matching rows in the other table (or intermediate result set). In a left outer join the left table is preserved, meaning that all of the matching and non-matching rows from the left table are returned into the result set. For the right table in the left outer join, only the matching rows are returned, similar to the inner join. Likewise, in a right outer join the right table is preserved, so that all of the matching and non-matching rows from the right table are returned into the result set and only the matching rows from the left table are returned. The syntax left versus right in a join operation is determined by the placement of the table in the SQL statement. In the example above, Table1 is the left table and Table2 is the right table. A full outer join combines the rows that are returned from both a left and a right outer join.
Graphical interfaces for visually depicting tables and their relationships are known in this field. These graphical interfaces generally depict the tables as boxes. The boxes may include the title of the table and a listing of the various column variables. Relationships between the tables are then depicted as lines that connect a column variable in one table to a column variable in another table. Numerous relationships may be depicted between the tables, and thus there is often more than one line connecting the tables together. In order to set the join type between two tables, a user typically selects one of the column relationships (i.e., picks a line that connects the tables) and then sets the joint type based on the selected relationship.
In a query tool that only permits inner joins, these known interfaces provide some utility. In a tool that enables more complex join operations, however, such as the various forms of outer joins discussed above, these known interfaces suffer from two significant problems. The first problem relates to the ambiguity created when there is more than one relationship between the tables and the user selects one of the relationships and sets the join type to an outer join. The problem here is how to configure the other non-selected relationship(s) that connect the two tables. One way to configure these other relationships is to assume that they represent AND conditions to the relationship that was set to an outer join. But another way is to configure these other relationships as separate joins. This outer join ambiguity problem inherently results from setting the join type based upon one of the column relationships.
The second problem with these known interfaces and tools relates to properly sequencing the order in which the tables are joined. Like the first problem, this second problem does not typically apply to a tool that only permits inner joins. For the more complex tool, however, which permits various forms of outer joins, the issue of proper table sequence is important. The final result set of a query may change depending upon the order (sequence) of the tables in the generated SQL when there are one or more outer joins. This occurs because the outer join operation internally produces an intermediate result set, which is then joined with subsequent tables in the join. Thus, changing the sequencing of the tables can change the query results when outer joins are involved. Known query tools and interfaces do not address this problem in any significant manner. Instead, these tools simply assume that the user has ordered the tables properly when the tables are selected during the process of building the query. This assumption, however, is often incorrect, and can lead to improper query results.
A system and method for configuring, sequencing and viewing joins in a query is provided. The system includes an advanced joins viewer that may be invoked from an SQL Query Tool. A graphical user interface associated with the advanced joins viewer displays a sequenced vertical tile list of each table and each intermediate result set in a particular query.
Through this interface, a user may re-sequence the ordering of the tables and thus reconfigure the intermediate result sets that are generated by the query. The advanced joins viewer enables the independent configuration of the join type and the join relationships for each of the intermediate result sets. An automatic sequencing algorithm is also provided for configuring the ordering of the tables such that the query produces valid SQL.
According to one aspect of the invention, a system for configuring, sequencing and displaying joins in a query is provided. The system is software based, and includes a sequence display window for depicting the sequence in which a plurality of tables are joined in the query, and for selecting an intermediate result set comprising two or more of the depicted tables; and a join configuration and display window for displaying the selected intermediate result set, including a table display for depicting the tables associated with the intermediate result set, a join type selector for configuring the type of join for the intermediate result set, and a relationships display window for depicting one or more relationships between the tables in the intermediate result set.
According to another aspect of the invention, a software tool is provided for creating SQL queries. The software tool includes a graphical user interface for selecting tables and for defining relationships between the tables, and an advanced joins viewer for depicting one or more intermediate result sets comprising two or more of the selected tables, and for configuring the joins between the tables independently of any defined relationships.
Still another aspect of the invention provides a method of configuring joins in an SQL query, comprising the steps of: (a) adding two or more tables to the query; (b) setting column relationships between the two or more tables; (c) selecting an intermediate result set from the two or more tables added to the query; (d) displaying the join type and column relationships for the selected intermediate result set; and (e) configuring the join type for the intermediate result set independently of the column relationships.
Another aspect of the invention provides a method of configuring joins in a database management system, comprising the steps of: (i) selecting a plurality of tables from a relational database; (ii) setting a plurality of relationships between the plurality of tables; (iii) displaying a sequenced listing of the selected tables; (iv) selecting an intermediate result set comprising two or more of the selected tables; (v) displaying a plurality of join types for the intermediate result set; and (vi) selecting a join type for the intermediate result set independently from the plurality of relationships.
Yet another aspect of the invention provides a method of automatically sequencing tables joined in an SQL query, comprising the steps of: (a) providing a join object for each table, wherein the join object includes a table relationships list that sets forth related tables; (b) providing a first list of the tables in the SQL query; (c) moving a first table from the first list into a second list and deleting the first table from the first list; (d) determining whether the first table has a related table in its table relationships list, and, if so, then moving the related table from the first list to the second list and deleting the related table from the first list; (e) repeating step (d) until a selected table has no related table in its table relationships list; (f) fetching a second table from the first list; (g) determining whether the second table has a related table in its table relationships list that has been moved to the second list, and, if so, then moving the related table from the first list to the second list and deleting the related table from the first list; and (h) repeating steps (d) through (g) until there are no tables remaining in the first list.
Another aspect of the invention provides a computer-implemented process for sequencing and configuring joins in an SQL query, comprising the steps of: (i) graphically building an SQL query having a plurality of tables, wherein the tables are related by a plurality of table relationships; (ii) automatically sequencing the tables in order to generate a valid SQL query; (iii) graphically depicting the sequencing of the tables as a plurality of intermediate result sets; (iv) selecting one of the intermediate result sets; and (v) configuring the join type for the selected intermediate result set independently from the plurality of table relationships.
Still another aspect of the invention provides a method of visually depicting and modifying the sequence of joins in a query, comprising the steps of: (a) generating a sequence display window comprising a plurality of tiles, wherein each tile corresponds to a table in the query that is joined to some other table; (b) ordering the tiles in a particular sequence; (c) selecting one of the tiles to be re-ordered in the sequence; (d) graphically moving the selected tile from a first position in the sequence to a second position in the sequence; and (e) re-ordering the tiles so that the selected tile is depicted in the second position in the sequence.
It should be noted that these are just some of the many aspects of the present invention. Other aspects not specified will become apparent upon reading the detailed description of the preferred embodiment set forth below.